﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using MySql.Data.MySqlClient;

namespace data_upload_HCollage
{
    public partial class UploadStudentForm : Form
    {
        public UploadStudentForm()
        {
            InitializeComponent();
        }

        private void btnBrowse_Click(object sender, EventArgs e)
        {
            if (upStd_openFileDialog.ShowDialog() == DialogResult.OK) 
            {
                txtFileLocation.Text = upStd_openFileDialog.FileName;
            }
               
        }

        private void btnUploadStd_Click(object sender, EventArgs e)
        {
            try
            {
               // OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + txtFileLocation.Text + ";Extended Properties=Excel 8.0");
                OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + txtFileLocation.Text + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES\";");
                OleDbDataAdapter da = new OleDbDataAdapter("select * from student", conn);
                DataTable dt = new DataTable();
                da.Fill(dt);
                int i = 0;
                MySqlConnection connMysql = new MySqlConnection(dbConnection.ConStr);
                connMysql.Open();

                string student_id = "";
                string academic_session = "";
                string group_id = cmbGroup_id.Text;
                string student_name = "";
                string fathername = "";
                string mothername = "";
                string ssc_board = "";
                string ssc_year = "";
                string ssc_group = "";
                string ssc_reg_no = "";
                string ssc_reg_year = "";
                string ssc_centre = "";
                string ssc_roll = "";
                string ssc_gpa = "";
                string ssc_opt = "";
                string ssc_school = "";
                string presentaddress = "";
                string permanentadd = "";
                string dob = "";
                string religion = "";
                string sex = "";
                string dateofadmission = "";
                string mother_lang = "";
                string phone_reg = "";
                string phone_off = "";
                string hsc_board = "";
                string hsc_year = "";
                string hsc_group = "";
                string hsc_reg_no = "";
                string hsc_reg_year = "";
                string hsc_centre = "";
                string hsc_roll = "";
                string hsc_gpa = "";
                string hsc_opt = "";
                string hsc_medium = ""; 

                foreach (DataRow row in dt.Rows)
                {
                    student_id = row["roll"].ToString();
                    academic_session = row["academic_session"].ToString().Replace(" ", "");
                    student_name = row["student_name"].ToString().ToString().Replace("'", @"\'"); ;

                    fathername = row["fathername"].ToString().ToString().Replace("'", @"\'"); ;
                    mothername = row["mothername"].ToString().ToString().Replace("'", @"\'"); ;

                    presentaddress = row["presentaddress"].ToString().Replace("'", @"\'"); ;
                    permanentadd = row["permanentadd"].ToString().Replace("'", @"\'"); ;
                    dob = row["dob"].ToString();
                    religion = row["religion"].ToString();
                    sex = row["sex"].ToString();
                    dateofadmission = row["dateofadmission"].ToString();
                    mother_lang = row["mother_lang"].ToString();
                    phone_reg = row["phone_reg"].ToString();
                    phone_off = row["phone_off"].ToString();


                    ssc_board = row["ssc_board"].ToString();
                    ssc_year = row["ssc_year"].ToString();
                    ssc_group = row["ssc_group"].ToString();
                    ssc_reg_no = row["ssc_reg_no"].ToString();
                    ssc_reg_year = row["ssc_reg_year"].ToString();
                    ssc_centre = row["ssc_centre"].ToString().ToString().Replace("'", @"\'"); ;
                    ssc_roll = row["ssc_roll"].ToString();
                    ssc_gpa = row["ssc_gpa"].ToString();
                    ssc_opt = row["ssc_opt"].ToString();
                    ssc_school = row["ssc_school"].ToString().Replace("'", @"\'");

                    hsc_board = row["hsc_board"].ToString();
                    hsc_year = row["hsc_year"].ToString();
                    hsc_group = row["hsc_group"].ToString();
                    hsc_reg_no = row["hsc_reg_no"].ToString();
                    hsc_reg_year = row["hsc_reg_year"].ToString();
                    hsc_centre = row["hsc_centre"].ToString().ToString().Replace("'", @"\'"); ;
                    hsc_roll = row["hsc_roll"].ToString();
                    hsc_gpa = row["hsc_gpa"].ToString();
                    hsc_opt = row["hsc_opt"].ToString();
                    hsc_medium = row["hsc_medium"].ToString();

                    String insert_sql = " insert into student( student_id,academic_session, group_id, student_name,fathername,mothername,"+
                    " presentaddress,permanentadd,dob,religion,sex,dateofadmission,mother_lang,phone_reg,phone_off)"
                           + " values('" + student_id + "','" + academic_session + "','" + group_id + "','" + student_name
                           + "','" + fathername + "','" + mothername + "','"+ presentaddress + "','" + permanentadd + "',"+
                           " str_to_date('" + dob + "','%d-%m-%Y')"+",'" + religion + "','" + sex + "'," + " str_to_date('" + dateofadmission + "','%d-%m-%Y')," 
                           + "'" + mother_lang + "','" + phone_reg + "','" + phone_off + "'"
                       + ")";
                        MySqlCommand Cmd_insert = new MySqlCommand(insert_sql, connMysql);
                        Cmd_insert.ExecuteNonQuery();

                        String insert_edu_sql = " insert into education( student_id,academic_session, "+
                               " ssc_board, ssc_year,ssc_group,ssc_reg_no,ssc_reg_year,ssc_centre,ssc_roll,ssc_gpa,ssc_opt,ssc_school, "+
                               " hsc_board,hsc_year,hsc_group,hsc_reg_no,hsc_reg_year,hsc_centre,hsc_roll,hsc_gpa,hsc_opt,hsc_medium)"
                               + " values('" + student_id + "','" + academic_session + "','" + ssc_board + "','" + ssc_year + "','" + ssc_group + "','" + ssc_reg_no + "','"
                               + ssc_reg_year + "','" + ssc_centre + "','" + ssc_roll + "','" + ssc_gpa + "','" + ssc_opt + "','" + ssc_school + "','"
                               + hsc_board + "','" + hsc_year + "','" + hsc_group + "','" + hsc_reg_no + "','" + hsc_reg_year + "','"
                               + hsc_centre + "','" + hsc_roll + "','" + hsc_gpa + "','" + hsc_opt + "','" + hsc_medium + "'" 
                           + ")";
                        MySqlCommand Cmd_edu_insert = new MySqlCommand(insert_edu_sql, connMysql);
                        Cmd_edu_insert.ExecuteNonQuery();

                        String insert_std_exam_merit_sql = " insert into std_exam_merit( student_id,academic_session)"
                                   + " values('" + student_id + "','" + academic_session + "'"
                                   + ")";
                        MySqlCommand Cmd_std_exam_merit = new MySqlCommand(insert_std_exam_merit_sql, connMysql);
                        Cmd_std_exam_merit.ExecuteNonQuery();


                    i++;
                }
                connMysql.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

            MessageBox.Show("Done!!!!!!!!");
        }

        private void UploadStudentForm_Load(object sender, EventArgs e)
        {
            cmbGroup_id.Text = "Please Select Group";
        }
    }
}
